'''
coding:utf-8
@Software:PyCharm
@Time:2022/10/1 23:17
@Author:xqh
@email:1042692670@qq.com
'''
import numpy as np
import pandas as pd
from PyQt5 import QtWidgets, QtGui
from PyQt5.QtWidgets import QApplication, QTableWidgetItem, QAbstractItemView
import sys
import os
import csv

from ui_xqh import Ui_Database_Window
from ui_mysql_xqh import Ui_Mysql_setting
from ui_database_setting import Ui_MainWindow_initial

import MySQLdb


class file_manipulate():
    def __init__(self, base_location: str = './file_system_data'):
        self.base_location = base_location
        if not os.path.exists(self.base_location):
            os.makedirs(self.base_location)
        self.init_file()

    def init_file(self):
        """
        初始化文件
        :return:
        """
        self.s_file_name = ["S.csv", "P.csv", "J.csv", "SPJ.csv"]
        self.file_name = [os.path.join(self.base_location, i) for i in self.s_file_name]

        header1 = ['SNO', 'SNAME', 'STATUS', 'CITY']
        data1 = [('S1', '精益', 20, '天津'), ('S2', '盛锡', 10, '北京'), ('S3', '东方红', 30, '北京'),
                 ('S4', '丰泰盛', 20, '天津'), ('S5', '为民', 30, '上海')]
        header2 = ['PNO', 'PNAME', 'COLOR', 'WEIGHT']
        data2 = [('P1', '螺母', '红', 12), ('P2', '螺栓', '绿', 17), ('P3', '螺丝刀', '蓝', 14),
                 ('P4', '螺丝刀', '红', 14), ('P5', '凸轮', '蓝', 40), ('P6', '齿轮', '红', 30)]
        header3 = ['JNO', 'JNAME', 'CITY']
        data3 = [('J1', '三建', '北京'), ('J2', '一汽', '长春'), ('J3', '弹簧厂', '天津'), ('J4', '造船厂', '天津'),
                 ('J5', '机车厂', '唐山'), ('J6', '无线电厂', '常州'), ('J7', '半导体厂', '南京')]
        header4 = ['SNO', 'PNO', 'JNO', 'QTY']
        data4 = [('S1', 'P1', 'J1', 200),
                 ('S1', 'P1', 'J3', 100),
                 ('S1', 'P1', 'J4', 700),
                 ('S1', 'P2', 'J2', 100),
                 ('S2', 'P3', 'J1', 400),
                 ('S2', 'P3', 'J2', 200),
                 ('S2', 'P3', 'J4', 500),
                 ('S2', 'P3', 'J5', 400),
                 ('S2', 'P5', 'J1', 400),
                 ('S2', 'P5', 'J2', 100),
                 ('S3', 'P1', 'J1', 200),
                 ('S3', 'P3', 'J1', 200),
                 ('S4', 'P5', 'J1', 100),
                 ('S4', 'P6', 'J3', 300),
                 ('S4', 'P6', 'J4', 200),
                 ('S5', 'P2', 'J4', 100),
                 ('S5', 'P3', 'J1', 200),
                 ('S5', 'P6', 'J2', 200),
                 ('S5', 'P6', 'J4', 500)]
        self.header_all = [header1, header2, header3, header4]
        data_all = [data1, data2, data3, data4]

        for i in range(len(self.file_name)):
            if not os.path.exists(self.file_name[i]):
                with open(self.file_name[i], 'w', encoding='utf-8', newline='') as fp:
                    # 写
                    writer = csv.writer(fp)
                    # 设置第一行标题头
                    writer.writerow(self.header_all[i])
                    # 将数据写入
                    writer.writerows(data_all[i])

    def insert_data(self, target_file: str, insert_data: list):
        with open(os.path.join(self.base_location, target_file), 'a', encoding='utf-8', newline='') as fp:
            # 写
            writer = csv.writer(fp)
            # 将数据写入
            writer.writerow(insert_data)

    def query_data(self, target_file: str, mode: str = 'all', attribute: str = None, query_data: str = None) -> list:
        """
        查找目标文件的数据,支持查询全部内容(all),根据单属性(single)或者多属性(joint)查询,多属性查询间隔符号为'-'
        :param target_file: st.'SPJ.csv'
        :param mode: 'all' or 'single' or 'joint',表示查找所有和根据单(多)个属性查找
        :param attribute: None or 'attribute',如果mode是all,那么为None,或者为某单个属性,或者为多个属性以’-‘相连
        :param query_data: 查找的数据,默认None,或者为某单个属性,或者为多个属性以’-‘相连
        :return: list
        """
        if mode == 'all':
            with open(os.path.join(self.base_location, target_file), encoding='utf-8') as fp:
                reader = csv.reader(fp)
                # 获取标题
                header = next(reader)
                # 遍历数据
                self.query_data_result = [i for i in reader]
            return self.query_data_result
        elif mode == 'single':
            self.query_data_result = []
            with open(os.path.join(self.base_location, target_file), encoding='utf-8') as fp:
                reader = csv.reader(fp)
                # 获取标题
                header = next(reader)
                # 遍历数据
                index_col = self.header_all[self.s_file_name.index(target_file)].index(attribute)
                for i in reader:
                    if i[index_col] == query_data:
                        self.query_data_result.append(i)
            return self.query_data_result
        elif mode == 'joint':
            attribute_list = attribute.split("-")
            query_list = query_data.split("-")
            with open(os.path.join(self.base_location, target_file), encoding='utf-8') as fp:
                reader = csv.reader(fp)
                # 获取标题
                header = next(reader)
                self.query_temp = reader
                self.query_data_result = []
                for cycle, attribute_ in enumerate(attribute_list):
                    # 遍历数据
                    index_col = self.header_all[self.s_file_name.index(target_file)].index(attribute_)
                    for i in self.query_temp:
                        if i[index_col] == query_list[cycle]:
                            self.query_data_result.append(i)
                    self.query_temp = self.query_data_result
                    self.query_data_result = []
                return self.query_temp

    def delete_data(self, target_file: str, attribute: str = None, query_data=None):
        """
        删除数据
        :param target_file:
        :param attribute: 属性
        :param query_data: 属性值
        :return:
        """
        df_base = pd.read_csv(os.path.join(self.base_location, target_file), header=0)
        if query_data == '':
            df_base.drop(df_base[df_base[attribute].isnull()].index, inplace=True)
        else:
            df_base.drop(df_base[df_base[attribute] == query_data].index, inplace=True)

        df_base.to_csv(os.path.join(self.base_location, target_file), index=False)

    def update_data(self, target_file: str, src_attribute: str = None, query_data=None, target_attribute: str = None,
                    action: str = '*1.1'):
        """
        更新数据，依据题意在此提供题意的action
        :param target_file:
        :param src_attribute: 源属性
        :param query_data: 查找的源数据
        :param target_attribute: 目标更新属性
        :param action: 采取的更新操作
        :return:
        """
        df_base = pd.read_csv(os.path.join(self.base_location, target_file), header=0)
        if action == '*1.1':
            df_base.loc[df_base[src_attribute] == query_data, target_attribute] *= 1.1
            df_base[[target_attribute]] = df_base[[target_attribute]].astype(float)
        df_base.to_csv(os.path.join(self.base_location, target_file), index=False)


class database_manipulate():
    def init_db(self, host='localhost', port=3306, user='root', passwd='3101876'):
        self.db = MySQLdb.connect(
            host=host,
            port=port,
            user=user,
            passwd=passwd,
            charset='utf8'
        )
        self.cursor = self.db.cursor()
        self.cursor.execute('SET autocommit=0;')

    def createdatabase(self):
        self.cursor.execute('DROP DATABASE IF EXISTS experiment;')
        self.cursor.execute('CREATE DATABASE experiment;')

    def createtable(self, tablename: list = ['']):
        self.cursor.execute('USE experiment;')
        if 'S' in tablename:
            self.cursor.execute(
                "CREATE TABLE S (SNO VARCHAR(3) PRIMARY KEY, SNAME VARCHAR(5), STATUS INT , CITY VARCHAR(3), CHECK (STATUS=10 OR STATUS=20 OR STATUS=30 OR STATUS=40));")
            self.cursor.execute(
                "INSERT INTO S VALUES ('S1', '精益', 20, '天津'),('S2', '盛锡', 10, '北京'),('S3', '东方红', 30, '北京'),('S4', '丰泰盛', 20, '天津'),('S5', '为民', 30, '上海');")

        if 'P' in tablename:
            self.cursor.execute(
                'CREATE TABLE P (PNO VARCHAR(3) PRIMARY KEY, PNAME VARCHAR(5), COLOR CHAR(1), WEIGHT INT);')
            self.cursor.execute(
                "INSERT INTO P VALUES ('P1', '螺母', '红', 12),('P2', '螺栓', '绿', 17),('P3', '螺丝刀', '蓝', 14),('P4', '螺丝刀', '红', 14),('P5', '凸轮', '蓝', 40),('P6', '齿轮', '红', 30);")

        if 'J' in tablename:
            self.cursor.execute('CREATE TABLE J (JNO VARCHAR(3) PRIMARY KEY, JNAME VARCHAR(5), CITY VARCHAR(3));')
            self.cursor.execute(
                "INSERT INTO J VALUES ('J1', '三建', '北京'),('J2', '一汽', '长春'),('J3', '弹簧厂', '天津'),('J4', '造船厂', '天津'),('J5', '机车厂', '唐山'),('J6', '无线电厂', '常州'),('J7', '半导体厂', '南京');")

        if 'SPJ' in tablename:
            self.cursor.execute(
                'CREATE TABLE SPJ (SNO VARCHAR(3), PNO VARCHAR(3), JNO VARCHAR(3), QTY INT, FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO));')
            self.cursor.execute(
                "INSERT INTO SPJ VALUES ('S1', 'P1', 'J1', 200),('S1', 'P1', 'J3', 100),('S1', 'P1', 'J4', 700),('S1', 'P2', 'J2', 100),('S2', 'P3', 'J1', 400),('S2', 'P3', 'J2', 200),('S2', 'P3', 'J4', 500),('S2', 'P3', 'J5', 400),('S2', 'P5', 'J1', 400),('S2', 'P5', 'J2', 100),('S3', 'P1', 'J1', 200),('S3', 'P3', 'J1', 200),('S4', 'P5', 'J1', 100),('S4', 'P6', 'J3', 300),('S4', 'P6', 'J4', 200),('S5', 'P2', 'J4', 100),('S5', 'P3', 'J1', 200),('S5', 'P6', 'J2', 200),('S5', 'P6', 'J4', 500);")

        self.db.commit()

    def database_chosen(self):
        self.cursor.execute('USE experiment;')

    def insert_data(self, target_file: str, insert_data: list):
        """
        插入数据,
        :param target_file:
        :param insert_data:
        :return:
        """
        insert_data = [f"'{i}'" for i in insert_data]
        self.cursor.execute(f"INSERT INTO {target_file} VALUES ({','.join(insert_data)});")

    def query_data(self, target_file: str, mode: str = 'all', attribute: str = None, query_data: str = None) -> list:
        """
        查找目标文件的数据,支持查询全部内容(all),根据单属性(single)或者多属性(joint)查询,多属性查询间隔符号为'-'
        :param target_file: st.'SPJ'
        :param mode: 'all' or 'single' or 'joint',表示查找所有和根据单(多)个属性查找
        :param attribute: None or 'attribute',如果mode是all,那么为None,或者为某单个属性,或者为多个属性以’-‘相连
        :param query_data: 查找的数据,默认None,或者为某单个属性,或者为多个属性以’-‘相连
        :return: list
        """
        if mode == 'all':
            self.cursor.execute(f'SELECT * FROM {target_file};')
            self.query_data_result = self.cursor.fetchall()
            return self.query_data_result
        elif mode == 'single':

            self.cursor.execute(f"SELECT * FROM {target_file} WHERE {attribute} = '{query_data}';")
            self.query_data_result = self.cursor.fetchall()
            return self.query_data_result
        elif mode == 'joint':
            attribute_list = attribute.split("-")
            query_list = query_data.split("-")
            meta_str_ = []
            for i in range(len(attribute_list)):
                meta_str_.append(f"{attribute_list[i]}='{query_list[i]}'")
            sql_str = " AND ".join(meta_str_)
            self.cursor.execute(f'SELECT * FROM {target_file} WHERE {sql_str};')
            self.query_data_result = self.cursor.fetchall()
            return self.query_data_result

    def delete_data(self, target_file: str, attribute: str = None, query_data=None):
        """
        删除数据
        :param target_file: st. S
        :param attribute:
        :param query_data:
        :return:
        """
        self.cursor.execute(f"DELETE FROM {target_file} WHERE {attribute} = '{query_data}';")

    def update_data(self, target_file: str, src_attribute: str = None, query_data=None, target_attribute: str = None,
                    action: str = '*1.1'):
        if action == '*1.1':
            self.cursor.execute(
                f"UPDATE {target_file} SET {target_attribute} = {target_attribute}{action}  WHERE {src_attribute} = '{query_data}';")

    def close_db(self):
        self.db.close()

    def db_commit(self):
        global mode_choose
        if mode_choose == 1:
            self.db.commit()

    def rollback(self):
        global mode_choose
        if mode_choose == 1:
            self.db.rollback()

    def start_affair(self):
        global mode_choose
        if mode_choose == 1:
            self.cursor.execute('BEGIN;')


database_control = database_manipulate()

mode_choose = 0  # 0:file system ; 1:database system


class Ui_windows(Ui_Database_Window):
    def __init__(self):
        super(Ui_windows, self).__init__()
        self.setupUi(self)  # 必须调用自动生成的两个方法
        self.affair_mode = 0  # 0:自动commit ; 1:启动事件

        self.slot_fun()
        self.file_control = file_manipulate()

        # 设置tableWidget只读，不允许修改
        self.tableWidget_S.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget_SPJ.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget_P.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget_J.setEditTriggers(QAbstractItemView.NoEditTriggers)

        # 工具栏
        self.actionfile_system.triggered.connect(self.mode_file)
        self.actiondatabase_system.triggered.connect(self.mode_database)
        self.actionbegin.triggered.connect(self.start_affair)
        self.actioncommit.triggered.connect(self.commit_affair)
        self.actionrollback.triggered.connect(self.affair_rollback)

    def slot_fun(self):
        ###S
        self.pushButton_S_insert.clicked.connect(self.ui_insert_data_S)
        self.pushButton_delete.clicked.connect(self.ui_delete_S_SPJ)
        self.pushButton_query_S.clicked.connect(self.ui_query_S)
        self.pushButton_queryall_S.clicked.connect(self.ui_queryall_S)
        ###SPJ
        self.pushButton_SPJ_insert.clicked.connect(self.ui_insert_data_SPJ)
        self.pushButton_modified.clicked.connect(self.ui_modified_SPJ)
        self.pushButton_queryall_SPJ.clicked.connect(self.ui_queryall_SPJ)
        ###P
        self.pushButton_query_P.clicked.connect(self.ui_query_P)
        ###J
        self.pushButton_query_J.clicked.connect(self.ui_query_J)
        pass

    def start_affair(self):
        global mode_choose
        if mode_choose == 1:
            database_control.start_affair()
            self.affair_mode = 1
            QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '已新建一个事务')

    def commit_affair(self):
        if mode_choose == 1:
            database_control.start_affair()
            database_control.db_commit()
            QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '已完成提交')


    def affair_rollback(self):
        global mode_choose
        if mode_choose == 1:
            database_control.rollback()
            QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '已完成回滚')

    def mode_file(self):
        QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '已选择文件管理系统')
        global mode_choose
        mode_choose = 0

    def mode_database(self):
        try:
            Ui_setting_core.show()
            global mode_choose
            mode_choose = 1
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'数据库设置存在问题,请重试\n{e}')

    def ui_insert_data_S(self):
        try:
            self.S_SNO = self.lineEdit_SNO.text()
            self.S_SNAME = self.lineEdit_SNAME.text()
            self.S_STATUS = self.lineEdit_STATUS.text()
            self.S_CITY = self.lineEdit_CITY.text()
            insert_S_data = [self.S_SNO, self.S_SNAME, self.S_STATUS, self.S_CITY]
            global mode_choose
            if mode_choose == 0:
                primary_sno = self.file_control.query_data("S.csv", mode='single', attribute='SNO',
                                                           query_data=self.S_SNO)
                if len(primary_sno) > 0:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确SNO,SNO为primary key')
                elif self.S_STATUS not in ['10', '20', '30', '40']:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确,STATUS只能为10|20|30|40')
                else:
                    self.file_control.insert_data("S.csv", insert_S_data)
                    QtWidgets.QMessageBox.information(self, '信息 v1.0', f'插入成功')
            elif mode_choose == 1:
                database_control.insert_data("S", insert_S_data)
                if self.affair_mode == 0:
                    self.commit_affair()
                QtWidgets.QMessageBox.information(self, '信息 v1.0', f'插入成功')
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'插入失败\n{e}')
        pass

    def ui_delete_S_SPJ(self):
        global mode_choose
        if mode_choose == 0:
            try:
                self.SNO_delete = self.lineEdit_sno_delete.text()
                SNO_query = self.file_control.query_data("S.csv", 'single', 'SNO', self.SNO_delete)
                if len(SNO_query) == 0:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', 'SNO不存在')
                else:
                    self.file_control.delete_data('SPJ.csv', 'SNO', self.SNO_delete)
                    self.file_control.delete_data('S.csv', 'SNO', self.SNO_delete)
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '删除完成')
            except Exception as e:
                QtWidgets.QMessageBox.information(self, '警告', f'删除失败\n{e}')
        elif mode_choose == 1:
            try:
                self.SNO_delete = self.lineEdit_sno_delete.text()
                SNO_query = database_control.query_data("S", 'single', 'SNO', self.SNO_delete)
                if len(SNO_query) == 0:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', 'SNO不存在')
                else:
                    database_control.delete_data('SPJ', 'SNO', self.SNO_delete)
                    database_control.delete_data('S', 'SNO', self.SNO_delete)
                    if self.affair_mode == 0:
                        self.commit_affair()
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '删除完成')
            except Exception as e:
                QtWidgets.QMessageBox.information(self, '警告', f'删除失败\n{e}')
        pass

    def ui_query_S(self):
        try:
            global mode_choose
            single_query_SNO = self.lineEdit_SNO_query.text()
            single_query_STATUS = self.lineEdit_STATUS_query.text()
            if '' == single_query_SNO and '' == single_query_STATUS:
                QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确,不输入空值')
            elif single_query_SNO != '' and '' == single_query_STATUS:
                if mode_choose == 0:
                    single_query_SNO_result = self.file_control.query_data('S.csv', 'single', 'SNO', single_query_SNO)
                else:
                    single_query_SNO_result = database_control.query_data('S', 'single', 'SNO', single_query_SNO)

                # 先获得总行数 row_count 清楚上一组数据
                row_count = self.tableWidget_S.rowCount()
                for r in range(0, row_count)[::-1]:
                    self.tableWidget_S.removeRow(r)
                for row_index, data in enumerate(single_query_SNO_result):
                    self.tableWidget_S.insertRow(row_index)
                    for col_index, col_data in enumerate(data):
                        self.tableWidget_S.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
            elif single_query_SNO == '' and single_query_STATUS != '':
                if mode_choose == 0:
                    single_query_STATUS_result = self.file_control.query_data('S.csv', 'single', 'STATUS',
                                                                              single_query_STATUS)
                else:
                    single_query_STATUS_result = database_control.query_data('S', 'single', 'STATUS',
                                                                             single_query_STATUS)
                # 先获得总行数 row_count 清楚上一组数据
                row_count = self.tableWidget_S.rowCount()
                for r in range(0, row_count)[::-1]:
                    self.tableWidget_S.removeRow(r)
                for row_index, data in enumerate(single_query_STATUS_result):
                    self.tableWidget_S.insertRow(row_index)
                    for col_index, col_data in enumerate(data):
                        self.tableWidget_S.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))

            elif single_query_SNO != '' and single_query_STATUS != '':
                query_data_joint = single_query_SNO + '-' + single_query_STATUS
                attribute_joint = 'SNO-STATUS'
                if mode_choose == 0:
                    joint_query_result = self.file_control.query_data('S.csv', 'joint', attribute_joint,
                                                                      query_data_joint)
                else:
                    joint_query_result = database_control.query_data('S', 'joint', attribute_joint, query_data_joint)

                # 先获得总行数 row_count 清楚上一组数据
                row_count = self.tableWidget_S.rowCount()
                for r in range(0, row_count)[::-1]:
                    self.tableWidget_S.removeRow(r)
                for row_index, data in enumerate(joint_query_result):
                    self.tableWidget_S.insertRow(row_index)
                    for col_index, col_data in enumerate(data):
                        self.tableWidget_S.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'查询失败\n{e}')

    def ui_queryall_S(self):
        # 先获得总行数 row_count 清楚上一组数据
        row_count = self.tableWidget_S.rowCount()
        # range(0, rowPosition)[::-1] 逆序循环
        for r in range(0, row_count)[::-1]:
            self.tableWidget_S.removeRow(r)
        ## 读取信息
        global mode_choose
        try:
            if mode_choose == 0:
                S_data = self.file_control.query_data('S.csv', mode='all')
            else:
                S_data = database_control.query_data('S', mode='all')
            for row_index, data in enumerate(S_data):
                self.tableWidget_S.insertRow(row_index)
                for col_index, col_data in enumerate(data):
                    self.tableWidget_S.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'查询失败\n{e}')
        pass

    def ui_insert_data_SPJ(self):
        global mode_choose
        self.SPJ_SNO = self.lineEdit_SNO_SPJ.text()
        self.SPJ_PNO = self.lineEdit_PNO_SPJ.text()
        self.SPJ_JNO = self.lineEdit_JNO_SPJ.text()
        self.SPJ_QTY = self.lineEdit_QTY_SPJ.text()
        insert_SPJ_data = [self.SPJ_SNO, self.SPJ_PNO, self.SPJ_JNO, self.SPJ_QTY]
        if mode_choose == 0:
            check_SNO = self.file_control.query_data("S.csv", 'single', 'SNO', query_data=self.SPJ_SNO)
            check_PNO = self.file_control.query_data("P.csv", 'single', 'PNO', query_data=self.SPJ_PNO)
            check_JNO = self.file_control.query_data("J.csv", 'single', 'JNO', query_data=self.SPJ_JNO)
            if len(check_SNO) == 0:
                QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确SNO,SNO为外码')
            elif len(check_PNO) == 0:
                QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确PNO,PNO为外码')
            elif len(check_JNO) == 0:
                QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', '请填写正确JNO,JNO为外码')
            else:
                try:
                    self.file_control.insert_data("SPJ.csv", insert_SPJ_data)
                    QtWidgets.QMessageBox.information(self, '信息 v1.0', f'插入成功')
                except Exception as e:
                    QtWidgets.QMessageBox.information(self, '警告', f'插入失败\n{e}')
        elif mode_choose == 1:
            try:
                database_control.insert_data("SPJ", insert_SPJ_data)
                if self.affair_mode == 0:
                    self.commit_affair()
                QtWidgets.QMessageBox.information(self, '信息 v1.0', f'插入成功')
            except Exception as e:
                QtWidgets.QMessageBox.information(self, '警告', f'插入失败\n{e}')
        pass

    def ui_modified_SPJ(self):
        SPJ_where_data = self.lineEdit_SNO_modified.text()
        global mode_choose
        try:
            if mode_choose == 0:
                SNO_query = self.file_control.query_data("SPJ.csv", 'single', 'SNO', SPJ_where_data)
                if len(SNO_query) == 0:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', 'SNO不存在')
                else:
                    self.file_control.update_data('SPJ.csv', "SNO", SPJ_where_data, 'QTY', '*1.1')
                    QtWidgets.QMessageBox.information(self, '信息 v1.0', f'修改成功')
            else:
                SNO_query = database_control.query_data("SPJ", 'single', 'SNO', SPJ_where_data)
                if len(SNO_query) == 0:
                    QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', 'SNO不存在')
                else:
                    database_control.update_data('SPJ', "SNO", SPJ_where_data, 'QTY', '*1.1')
                    QtWidgets.QMessageBox.information(self, '信息 v1.0', f'修改成功')
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '提醒信息 v1.0', f'更新错误,具体报错如下\n{e}')
        pass

    def ui_queryall_SPJ(self):
        # 先获得总行数 row_count 清楚上一组数据
        row_count = self.tableWidget_SPJ.rowCount()
        # range(0, rowSPJosition)[::-1] 逆序循环
        for r in range(0, row_count)[::-1]:
            self.tableWidget_SPJ.removeRow(r)

        ## 读取信息
        if mode_choose == 0:
            SPJ_data = self.file_control.query_data('SPJ.csv', mode='all')
        else:
            SPJ_data = database_control.query_data('SPJ', mode='all')
        for row_index, data in enumerate(SPJ_data):
            self.tableWidget_SPJ.insertRow(row_index)
            for col_index, col_data in enumerate(data):
                self.tableWidget_SPJ.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
        pass

    def ui_query_P(self):
        global mode_choose
        # 先获得总行数 row_count 清楚上一组数据
        row_count = self.tableWidget_P.rowCount()
        # range(0, rowPosition)[::-1] 逆序循环
        for r in range(0, row_count)[::-1]:
            self.tableWidget_P.removeRow(r)

        ## 读取信息
        if mode_choose == 0:
            P_data = self.file_control.query_data('P.csv', mode='all')
        else:
            P_data = database_control.query_data('P', mode='all')
        for row_index, data in enumerate(P_data):
            self.tableWidget_P.insertRow(row_index)
            for col_index, col_data in enumerate(data):
                self.tableWidget_P.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
        pass

    def ui_query_J(self):
        global mode_choose
        # 先获得总行数 row_count 清楚上一组数据
        row_count = self.tableWidget_J.rowCount()
        # range(0, rowJosition)[::-1] 逆序循环
        for r in range(0, row_count)[::-1]:
            self.tableWidget_J.removeRow(r)

        ## 读取信息
        if mode_choose == 0:
            J_data = self.file_control.query_data('J.csv', mode='all')
        else:
            J_data = database_control.query_data('J', mode='all')
        for row_index, data in enumerate(J_data):
            self.tableWidget_J.insertRow(row_index)
            for col_index, col_data in enumerate(data):
                self.tableWidget_J.setItem(row_index, col_index, QTableWidgetItem(str(col_data)))
        pass


class Ui_setting(Ui_Mysql_setting):
    def __init__(self):
        super(Ui_setting, self).__init__()
        self.setupUi(self)
        self.pushButton_Confirm.clicked.connect(self.get_setting)
        self.actioninitial.triggered.connect(self.initial_ui)

    def get_setting(self):
        try:
            global database_control
            host = self.lineEdit_HOST.text()
            port = self.lineEdit_PORT.text()
            user = self.lineEdit_USER.text()
            passwd = self.lineEdit_PW.text()
            database_control.init_db(host, int(port), user, passwd)
            self.create_tablename = []
            if S_checkBox == True:
                self.create_tablename.append('S')
            if P_checkBox == True:
                self.create_tablename.append('P')
            if J_checkBox == True:
                self.create_tablename.append('J')
            if SPJ_checkBox == True:
                self.create_tablename.append('SPJ')
            database_control.createtable(self.create_tablename)
            QtWidgets.QMessageBox.information(self, '信息', f'登录成功,使用数据库experiment')
            Ui_setting_core.close()
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'{e}')

    def initial_ui(self):
        try:
            Ui_initial_core.show()
        except Exception as e:
            QtWidgets.QMessageBox.information(self, '警告', f'{e}')


S_checkBox = False
J_checkBox = False
SPJ_checkBox = False
P_checkBox = False


class Ui_initial_database(Ui_MainWindow_initial):
    def __init__(self):
        super(Ui_initial_database, self).__init__()
        self.setupUi(self)
        self.pushButton_initial.clicked.connect(self.get_status_initial)

    def get_status_initial(self):
        try:
            global S_checkBox
            S_checkBox = self.S_checkBox_init.isChecked()
            global J_checkBox
            J_checkBox = self.J_checkBox_init.isChecked()
            global SPJ_checkBox
            SPJ_checkBox = self.SPJ_checkBox_init.isChecked()
            global P_checkBox
            P_checkBox = self.P_checkBox_init.isChecked()
            Ui_initial_core.close()
        except Exception as e:
            QtWidgets.QMessageBox.warning(self, '警告', f'{e}')


if __name__ == '__main__':
    app = QApplication(sys.argv)
    Ui_Core = Ui_windows()
    Ui_setting_core = Ui_setting()
    Ui_initial_core = Ui_initial_database()
    Ui_Core.setWindowIcon(QtGui.QIcon('upc.ico'))  # 设置窗体标题图标
    Ui_setting_core.setWindowIcon(QtGui.QIcon('upc.ico'))  # 设置窗体标题图标
    Ui_initial_core.setWindowIcon(QtGui.QIcon('upc.ico'))  # 设置窗体标题图标
    Ui_Core.show()
    sys.exit(app.exec_())
